package com.engc.common.utils;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {

	public static Connection getCon(String dirvername, String dbUrl, String dbUsername, String dbPwd) throws Exception {
		try {
			Class.forName(dirvername);
			Connection con = DriverManager.getConnection(dbUrl, dbUsername, dbPwd);
			return con;
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
	}

	/**
	 * 关闭资源
	 * 
	 * @param rs
	 * @param st
	 * @param conn
	 * @throws SQLException
	 */
	public static void free(ResultSet rs, Statement st, Connection conn) throws SQLException {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			throw new SQLException();
		} finally {
			try {
				if (st != null)
					st.close();
			} catch (SQLException e) {
				throw new SQLException();
			} finally {
				if (conn != null)
					try {
						conn.close();
					} catch (Exception e) {
						throw new SQLException();
					}
			}
		}
	}

	static String getFieldList(Field[] fs) {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < fs.length - 1; i++) {
			sb.append(fs[i].getName() + ", ");
		}
		sb.append(fs[fs.length - 1].getName());
		return sb.toString();
	}

	public static String getProperedSQL(Object obj) {
		StringBuffer values = new StringBuffer();
		Class<? extends Object> c = obj.getClass();
		values.append("insert into ");
		values.append(c.getSimpleName().toLowerCase());
		values.append(" (");
		values.append(getFieldList(c.getDeclaredFields()));
		values.append(") ");
		values.append("values");
		values.append("( ");

		int fLength = c.getDeclaredFields().length;
		for (int i = 0; i < fLength - 1; i++) {
			values.append("?, ");
		}
		values.append("?");
		values.append(")");

		return values.toString();
	}

	public static boolean setInsertProperedStatement(PreparedStatement pst, Object obj) {

		boolean flag = false;

		Class<? extends Object> c = obj.getClass();
		try {
			Field[] fs = c.getDeclaredFields();
			Method m = null;

			// -------------赋值
			ParameterMetaData psn = pst.getParameterMetaData();
			int plength = psn.getParameterCount();
			for (int i = 0; i < plength; i++) {
				String mName = getMethodName(fs[i]);

				m = c.getDeclaredMethod(mName);

				pst.setObject(i + 1, m.invoke(obj));
			}
			flag = true;
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		return flag;
	}

	public static boolean setUpadateProperedStatement(PreparedStatement pst, Object obj) {

		boolean flag = false;

		Class<? extends Object> c = obj.getClass();
		try {
			Field[] fs = c.getDeclaredFields();
			Method m = null;

			// -------------赋值
			ParameterMetaData psn = pst.getParameterMetaData();
			int plength = psn.getParameterCount();
			for (int i = 1; i < plength; i++) {
				String mName = getMethodName(fs[i]);
				m = c.getDeclaredMethod(mName);
				pst.setObject(i, m.invoke(obj));

			}

			String mName = getMethodName(fs[0]);
			m = c.getDeclaredMethod(mName);
			pst.setObject(psn.getParameterCount(), m.invoke(obj));

			flag = true;
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		return flag;
	}

	public static String getUpdateSQLById(Object obj) {
		// sql = "update computer set name=?,price =?  where id = ? ";
		StringBuffer values = new StringBuffer();
		Class<? extends Object> c = obj.getClass();
		values.append(" update ");
		values.append(c.getSimpleName());
		values.append(" set ");
		values.append(getUpdateString(c));
		values.append(" where ");
		values.append(getConditionString(obj));

		return values.toString();
	}

	private static Object getConditionString(Object obj) {
		StringBuffer values = new StringBuffer();
		Field f = obj.getClass().getDeclaredFields()[0];
		values.append(f.getName());
		values.append(" = ? ");
		return values.toString();
	}

	private static String getUpdateString(Class<? extends Object> c) {
		StringBuffer values = new StringBuffer();
		Field[] fs = c.getDeclaredFields();
		for (int i = 1; i < fs.length - 1; i++) {
			values.append(fs[i].getName());
			values.append(" =?,");
		}
		values.append(fs[fs.length - 1].getName());
		values.append(" = ? ");
		return values.toString();
	}

	private static String getMethodName(Field f) {

		int step = 1 << 5;
		String fn = f.getName();
		char ch = (char) (fn.charAt(0) - step);
		String methodNames = "get" + ch + fn.substring(1);

		return methodNames;
	}

	// 通过rs给obj赋值。
	public static <T> T getObject(Class<T> c, ResultSet rs) {
		T obj = null;
		try {
			obj = c.newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}

		try {
			if (c == null) {
				throw new NullPointerException();
			}

			Field[] fs = obj.getClass().getDeclaredFields();
			for (int i = 0; i < fs.length; i++) {
				String n = fs[i].getName();
				setField(fs[i], rs.getObject(n), obj);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}
		return obj;
	}

	static <T> void setField(Field field, Object value, T obj) {
		int step = 1 << 5;
		String fn = field.getName();
		char ch = (char) (fn.charAt(0) - step);
		String methodN = "set" + ch + fn.substring(1);
		try {
			Method m = obj.getClass().getDeclaredMethod(methodN, field.getType());
			m.invoke(obj, value);
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
	}

	public static String getSelectSQL(Class<?> c) {
		String s = getFieldList(c.getDeclaredFields());
		return "select " + s + " from " + c.getSimpleName();
	}

	public static <T> String getDeleteSQL(Class<T> c) {
		return "delete  from " + c.getSimpleName();
	}

}
